'use strict';

const Controller = require('egg').Controller;

class HomeController extends Controller {
  async index() {
    await this.ctx.render('index');
  }
  //获取文章列表
  async getArticleList() {
    let sql = `SELECT 
    article.id as id,
    article.title as title,
    article.introduce as introduce,
    FROM_UNIXTIME(article.addTime,'%Y-%m-%d') as addTime,
    article.view_count as view_count,
    article.article_url as article_url,
    type.typeName as typeName
    FROM article LEFT JOIN type  type ON article.type_id = type.id ORDER BY addTime desc`;
    const results = await this.app.mysql.query(sql);
    this.ctx.body = { data: results };
  }
  //同步ID获取文章详情
  async getArticleById() {
    const id = this.ctx.params.id;
    let sql = `SELECT 
    article.id as id,
    article.title as title,
    article.introduce as introduce,
    article.article_content as article_content,
    FROM_UNIXTIME(article.addTime,'%Y-%m-%d') as addTime,
    article.view_count as view_count,
    type.typeName as typeName
    FROM article LEFT JOIN type  type ON article.type_id = type.id where article.id = ${id}`;
    let result = await this.app.mysql.query(sql);
    let count = result[0].view_count + 1;
    await this.app.mysql.query('update article set view_count = ? where id = ?', [count, id]);
    this.ctx.body = result;
  }
  //获取类别名称和编号
  async getTypeInfo() {
    const result = await this.app.mysql.select('nav');
    this.ctx.body = { data: result };
  }
  //根据类别ID获取文章列表
  async getListById() {
    let id = this.ctx.params.id;
    let sql = `SELECT 
    article.id as id,
    article.title as title,
    article.introduce as introduce,
    FROM_UNIXTIME(article.addTime,'%Y-%m-%d') as addTime,
    article.view_count as view_count,
    type.typeName as typeName
    FROM article LEFT JOIN type  type ON article.type_id = type.id where type_id = ${id}`;
    const results = await this.app.mysql.query(sql);
    this.ctx.body = { data: results };
  }
  //修改网站访问数
  async modWebCountById() {
    let sql = `UPDATE web_visit sit SET visit_count=visit_count+1 WHERE id=1`;
    await this.app.mysql.query(sql);
    const results = await this.app.mysql.query(`SELECT visit_count FROM web_visit WHERE id=1`);
    this.ctx.body = { data: results[0].visit_count };
  }
  // 获取随笔列表
  async getEssayList() {
    let data = this.ctx.query;
    let total = await this.app.mysql.query('SELECT COUNT(*) total FROM essay');
    let sql = `SELECT 
    essay.id as id,
    essay.content as content,
    FROM_UNIXTIME(essay.addTime,'%Y-%m-%d %H:%i:%S') as addTime,
    essay.essay_url as essay_url
    FROM essay ORDER BY essay.addTime DESC limit ${(data.current - 1) * data.pageSize},${data.pageSize}`;
    const results = await this.app.mysql.query(sql);
    this.ctx.body = { data: results, total: total[0].total };
  }
  // 游客添加留言信息
  async addMessage() {
    let tmpMessage = this.ctx.request.body;
    const result = await this.app.mysql.insert('message', tmpMessage);
    const insertSuccess = result.affectedRows === 1;
    if (insertSuccess) {
      this.ctx.body = {
        msg: '留言发布成功，待管理员审核',
        status: 200
      }
    } else {
      this.ctx.body = {
        msg: '留言发布失败',
        status: 400
      }
    }
  }
  // 获取留言列表（每次获取五条）
  async getMessageList() {
    let page = this.ctx.query.page;
    let total = await this.app.mysql.query('SELECT COUNT(*) total FROM message WHERE status=1');
    let sql = `SELECT 
    id,
    nickName,
    content,
    FROM_UNIXTIME(message.addTime,'%Y-%m-%d %H:%i:%S') as addTime
    FROM message WHERE status=1 ORDER BY addTime DESC limit 0,${page * 5}`;
    const results = await this.app.mysql.query(sql);
    let length = results.length
    results.forEach(element => {
      element['sort'] = length;
      length--;
    });
    this.ctx.body = { data: results, total: total[0].total };
  }
}

module.exports = HomeController;
